<?php
// +------------------+--------------+------+-----+---------+----------------+
// | Field            | Type         | Null | Key | Default | Extra          |
// +------------------+--------------+------+-----+---------+----------------+
// | id               | int(11)      | NO   | PRI | NULL    | auto_increment |
// | name             | varchar(60)  | NO   |     |         |                |
// | password         | varchar(64)  | NO   |     |         |                |
// | nickname         | varchar(50)  | YES  |     |         |                |
// | email            | varchar(50)  | YES  |     |         |                |
// | url              | varchar(100) | YES  |     |         |                |
// | avatar           | varchar(200) | YES  |     |         |                |
// | gender           | varchar(10)  | YES  |     |         |                |
// | birthday         | date         | YES  |     | NULL    |                |
// | signature        | varchar(200) | YES  |     |         |                |
// | last_login_ip    | bigint(20)   | YES  |     | 0       |                |
// | last_login_time  | int(11)      | YES  |     | 0       |                |
// | activation_key   | varchar(60)  | YES  |     |         |                |
// | status           | tinyint(1)   | YES  |     | 1       |                |
// | active_score     | int(11)      | YES  |     | 0       |                |
// | user_type        | varchar(20)  | YES  |     |         |                |
// | active_coin      | int(11)      | YES  |     | 0       |                |
// | mobile           | varchar(20)  | YES  |     |         |                |
// | create_time      | int(11)      | YES  |     | 0       |                |
// | update_time      | int(11)      | YES  |     | 0       |                |
// | editor_id        | int(11)      | YES  |     | 0       |                |
// | manager_group_id | int(11)      | YES  |     | 0       |                |
// | is_locked        | tinyint(1)   | YES  |     | 0       |                |
// | total_score      | int(11)      | YES  |     | 0       |                |
// | total_coin       | int(11)      | YES  |     | 0       |                |
// | member_group_id  | int(11)      | YES  |     | 0       |                |
// | province         | int(11)      | YES  |     | 0       |                |
// | city             | int(11)      | YES  |     | 0       |                |
// | address          | varchar(255) | YES  |     |         |                |
// | description      | varchar(255) | YES  |     |         |                |
// +------------------+--------------+------+-----+---------+----------------+
namespace app\common\model;
use think\Config;
use think\Loader;
// use app\common\validate\User;
use app\common\model\Base;
use app\common\model\AuthGroup;
use app\common\model\ScoreLog;
use app\common\model\GroupAccess;
use think\Request;
class User extends Base{
  protected $auto = ['editor_id'];

  /**
   * 添加新用户
   * @Author zhanghong
   * @Date   2016-12-22
   * @param  [string]     $user_type 用户类型
   * @param  [array]      $data      用户数据
   * @return [array]                     操作是否成功
   */
  public function addUser($data){
    $res = ["status" => false, "id" => 0, "msg" => "添加用户失败"];
  
    $validate = Loader::validate("User");
    // user_type是manager or member
    if($validate->scene($data["user_type"])->check($data)){
      $data["password"] = $this->md5Password($data["password"]);
      $this->data($data)->allowField(true)->save();
      $id = $this->id;

      if(isset($data["active_score"])){
        $inc_score = intval($data["active_score"]);
      }else{
        $inc_score = 0;
      }
      if(isset($data["active_coin"])){
        $inc_coin = intval($data["active_coin"]);
      }else{
        $inc_coin = 0;
      }
      // 记录用户积分
      $this->updateUserScore($id, $inc_score, $inc_coin, "创建用户", ["reset_score" => true, "reset_coin" => true]);

      // 更新管理员级
      if($data["user_type"] == "manager" && isset($data["manager_group_id"])){
        $AccessModel = new GroupAccess();
        $access_res = $AccessModel->createOrUpdateUserAccess($id, $data["manager_group_id"], "manager");
      }

      $res = ["status" => true, "id" => $id, "msg" => "添加用户成功"];
    }else{
      $res["msg"] = $validate->getError();
    }

    return $res;
  }

  /**
   * 更新用户基本信息
   * @Author zhanghong
   * @Date   2016-12-23
   * @param  string     $user_type 用户类型
   * @param  array      $data      用户信息
   * @param  integer    $id        用户ID
   * @return array                 更新结果
   */
  public function updateBaseInfo($data, $id = 0){
    $res = ["status" => false, "msg" => "更新用户信息成功", "user" => NULL];
    if(!empty($data["id"])){
      $id = intval($data["id"]);
    }

    if($id <= 0){
      $res["msg"] = "用户ID不正确";
      return $res;
    }

    if(empty($data["user_type"])){
      $res["msg"] = "用户类型不能为空";
      return $res;
    }else if(!in_array($data["user_type"], ["member", "manager"])){
      $res["msg"] = "用户类型不正确";
      return $res;
    }

    //如果在更新字段里不包含id，而是放在where条件里, 邮箱唯一验证会失败
    $allow_data = ["id" => $id];
    // 只能修改以下几个字段
    $all_fields = ["confirm_password", "password", "nickname", "url", "avatar", "gender", "birthday", "coin", "manager_group_id", "is_locked", "email", "name", "province", "city", "address", "description"];
    foreach ($data as $key => $value) {
      if(in_array($key, $all_fields)){
        $allow_data[$key] = $data[$key];
      }
    }

    $validate = Loader::validate("User");
    // user_type是manager or member
    if($validate->scene($data["user_type"])->check($allow_data)){
      if(!empty($allow_data["password"])){
        $allow_data["password"] = $this->md5Password($allow_data["password"]);
        unset($allow_data["confirm_password"]);
      }
      
      $this->allowField(true)->update($allow_data);

      $user = $this->where("id", $id)->find();
      if(isset($data["active_score"])){
        $inc_score = $data["active_score"] - $user["active_score"];
      }else{
        $inc_score = 0;
      }
      if(isset($data["active_coin"])){
        $inc_coin = $data["active_coin"] - $user["active_coin"];
      }else{
        $inc_coin = 0;
      }
      if($inc_score != 0 || $inc_coin != 0){
        // 更新会员等级信息
        $this->updateUserScore($id, $inc_score, $inc_coin, "更新用户积分");
      }

      // 更新管理员级
      if($data["user_type"] == "manager" && isset($data["manager_group_id"])){
        $AccessModel = new GroupAccess();
        $access_res = $AccessModel->createOrUpdateUserAccess($id, $data["manager_group_id"], "manager");
      }

      $res = ["status" => true, "msg" => "更新用户信息成功"];
    }else{
      $res["msg"] = $validate->getError();
    }

    return $res;
  }

  /**
   * 更新用户Score/Coin
   * @Author zhanghong
   * @Date   2017-01-09
   * @param  integer    $id        用户ID
   * @param  integer    $inc_score 增加score
   * @param  integer    $inc_coin  增加coin
   * @param  string     $comment   备注信息
   * @param  array      $option    日志扩展信息
   * @return array
   */
  private function updateUserScore($id, $inc_score, $inc_coin, $comment, $option = []){
    $res = ["status" => false, "msg" => "更新用户积分失败", "data" => []];

    $user = $this->where("id", $id)->find();
    if(empty($user)){
      $res["msg"] = "用户不存存";
      return $res;
    }

    $data = [];
    $log_data = [];
    if(isset($option["reset_score"])){
      $data["total_score"] = $inc_score;
      $data["active_score"] = $inc_score;
    }else if($inc_score > 0){
      $data["total_score"] = $user["total_score"]+$inc_score;
      $data["active_score"] = $user["active_score"]+$inc_score;
    }else if($inc_score < 0){
      $data["total_score"] = $user["total_score"];
      $data["active_score"] = $user["active_score"]+$inc_score;
    }

    if(isset($option["reset_coin"])){
      $data["total_coin"] = $inc_coin;
      $data["active_coin"] = $inc_coin;
    }else if($inc_coin > 0){
      $data["total_coin"] = $user["total_coin"]+$inc_coin;
      $data["active_coin"] = $user["active_coin"]+$inc_coin;
    }else if($inc_coin < 0){
      $data["total_coin"] = $user["total_coin"];
      $data["active_coin"] = $user["active_coin"]+$inc_coin;
    }

    if(empty($data)){
      $res["msg"] = "积分更新数据为空";
      return $res;
    }

    $data["id"] = $id;
    $validate = Loader::validate("User");
    if($validate->scene("score_coin")->check($data)){
      if(isset($data["total_score"])){
        $current_total_score = $data["total_score"];
      }else{
        $current_total_score = $user["total_score"];
      }
      // 计算用户当前等级
      $GroupModel = new AuthGroup();
      $group_item = $GroupModel->memberGroupItem($current_total_score);
      if(empty($group_item)){
        // 没有符合条件的注册会员组，不更新当前用户的group_id
        // $group_id = $user["member_group_id"];
      }else{
        $group_id = $group_item["id"];
        $data["member_group_id"] = $group_id;
      }
      $this->update($data);

      if(isset($data["member_group_id"])){
        // 创建或更新用户的GroupAccess记录
        $AccessModel = new GroupAccess();
        $access_res = $AccessModel->createOrUpdateUserAccess($id, $group_id, "member");
      }

      // 记录积分更新日志
      $data["user_id"] = $id;
      $data["inc_score"] = $inc_score;
      $data["inc_coin"] = $inc_coin;
      $data["comment"] = $comment;
      unset($data["id"]);
      // 记录option字段
      $data = array_merge($option, $data);
      $LogModel = new ScoreLog();
      $log_id = $LogModel->addLog($data);
      $res = ["status" => true, "msg" => "更新用户积分成功", "data" => ["log_id" => $log_id]];
    }else{
      $res["msg"] = $validate->getError();
    }
    return $res;
  }

  /**
   * 重新加载用户登录信息
   * @Author zhanghong
   * @Date   2017-03-12
   * @param  integer    $id        用户ID
   * @param  string     $user_type 用户类型
   * @return array
   */
  public function reloadUserInfo($id, $user_type = NULL){
    $param = [];
    $param["id"] = $id;
    if(!empty($user_type)){
      $param["user_type"] = $user_type;
    }
    return $this->findLoginInfo($param);
  }

  /**
   * 最近登录用户列表
   * @Author zhanghong
   * @Date   2017-05-04
   * @param  integer    $limit_count 返回数量
   * @return array                   User List
   */
  public function lastLoginSelect($limit_count = 12){
    $select_fields = [
      "id", "nickname", "email", "url", "avatar", "gender", "birthday", "active_score", "active_coin", "mobile"
    ];

    $list = $this->field($select_fields)
                 ->where("status", true)->where("is_locked", 0)
                 ->order("last_login_time DESC")
                 ->limit($limit_count)
                 ->select();
    return $list;
  }

  /**
   * 用户登录信息
   * @Author zhanghong
   * @Date   2017-01-01
   * @param  array      $param  搜索参数
   * @return array              User
   */
  private function findLoginInfo($param){
    $user = array();
    if(empty($param)){
      return $user;
    }

    $map_count = 0;
    $this->field(["id", "nickname", "email", "url", "avatar", "gender", "birthday", "last_login_ip", "last_login_time", "active_score", "active_coin", "mobile", "create_time", "update_time", "user_type"]);
                 
    foreach ($param as $key => $value) {
      switch ($key) {
      case "password":
        $password = $this->md5Password($value);
        $this->where("password", $password);
        $map_count++;
        break;
      default:
        $this->where($key, $value);
        $map_count++;
        break;
      }
    }

    // 默认参数（status=true, is_locked=false）
    $this->where("status", true)->where("is_locked", 0);

    if($map_count > 0){
      $user = $this->find();
    }

    return $user;
  }

  /**
   * 后台管理员登录
   * @Author zhanghong
   * @Date   2016-12-22
   * @param  [string]     $mobile   登录名(手机号码)
   * @param  [string]     $password 登录密码
   * @return [array]                登录用户信息
   */
  public function managerLogin($mobile, $password){
    if(empty($mobile)){
      return NULL;
    }
    
    $param = ["mobile" => $mobile, "password" => $password, "user_type" => "manager"];
    $user = $this->findLoginInfo($param);

    if(!empty($user)){
      $request = Request::instance();
      $data = ["last_login_ip" => ip2long($request->ip()), "last_login_time" => time()];
      $user->save($data, ['id' => $user["id"]]);
    }
    return $user;
  }

  /**
   * 前台用户登录
   * @Author zhanghong
   * @Date   2016-12-22
   * @param  [string]     $mobile   登录名(手机号码)
   * @param  [string]     $password 登录密码
   * @return [array]                登录用户信息
   */
  public function memberLogin($mobile, $password){
    if(empty($mobile)){
      return NULL;
    }

    $param = ["mobile" => $mobile, "password" => $password];
    $user = $this->findLoginInfo($param);

    if(!empty($user)){
      $request = Request::instance();
      $data = ["last_login_ip" => ip2long($request->ip()), "last_login_time" => time()];
      $user->save($data, ['id' => $user["id"]]);
    }
    return $user;
  }

  /**
   * 更新用户密码
   * @Author zhanghong
   * @Date   2016-12-22
   * @param  [integer]     $id           用户ID
   * @param  [array]       $data         密码信息
   * @param  boolean       $only_active  是否只查询有效用户
   * @return [array]                     操作是否成功
   */
  public function changePassword($id, $data, $only_active = true){
    $res = ["status" => false, "msg" => "密码更新失败"];

    // data包含 password(新密码), old_password(旧密码)，confirm_password(确认密码)
    $id = intval($id);

    $password = $this->md5Password($data["old_password"]);
    $this->where("id", $id);
    $this->where("password", $password);
    if($only_active){
      $this->where("status", 1);
    }

    $user = $this->find();
    if(empty($user)){
      $res["status"] = "旧密码错误";
      return $res;
    }

    $validate = Loader::validate("User");
    // 验证密码是否符合validate
    if($validate->scene($user["user_type"])->check($data)){
      $data["password"] = $this->md5Password($data["password"]);
      $this->allowField(true)->save($data, ["id" => $user["id"]]);
      $res = ["status" => true, "msg" => "密码更新成功"];
    }else{
      $res["msg"] = $validate->getError();
    }

    return $res;
  }

  /**
   * 重置用户密码
   * @Author zhanghong
   * @Date   2017-05-16
   * @param  [type]     $data        [description]
   * @param  boolean    $only_active [description]
   * @return [type]                  [description]
   */
  public function retrievePassword($data, $only_active = true){
    $res = ["status" => false, "msg" => "密码更新失败"];

    // data包含 mobile(手机号) password(新密码), confirm_password(确认密码)
    
    $mobile = $data["mobile"];
    $this->where("mobile", $mobile);
    if($only_active){
      $this->where("status", 1);
    }

    $user = $this->find();
    $data['id'] = $user['id'];
    if(empty($user)){
      $res = ["status" => false, "msg" => "没有此帐号"];
      return $res;
    }
  
    $validate = Loader::validate("User");
    // 验证密码是否符合validate
    if($validate->scene($user["user_type"])->check($data)){
      $data["password"] = $this->md5Password($data["password"]);
      $this->allowField(true)->save($data, ["id" => $user["id"]]);
      $res = ["status" => true, "msg" => "重置密码成功"];
    }else{
      $res["msg"] = $validate->getError();
    }

    return $res;
  }



  /**
   * 查询方法
   * @Author zhanghong
   * @Date   2016-12-22
   * @param  [string]     $user_type 用户类型
   * @param  [array]      $param     查询参数
   * @param  [integer]    $page      当前页码
   * @param  integer      $page_rows 每页显示的记录数
   * @return \think\paginator\Collection 
   */
  public function memberSelect($param, $page_rows = 15){
    $prefix = Config::get("database.prefix");
    $this->where("{$prefix}user.user_type", "member");

    $config = [];
    foreach ($param as $key => $value) {
      if(!is_array($value)){
        $value = trim($value);
      }
      switch ($key) {
      case "status":
        $this->where("{$prefix}user.status", intval($value));
        break;
      case "keyword":
        if(empty($value)){
          continue;
        }else{
          $keyword = "%{$value}%";
        }
        $this->where("{$prefix}user.nickname|{$prefix}user.email|{$prefix}user.mobile", "like", $keyword);
        break;
      }
    }

    $config["query"] = $param;

    $this->join("{$prefix}group_access ma", "ma.uid={$prefix}user.id AND ma.user_type='member'", "LEFT")
         ->join("{$prefix}auth_group mbr_group", "ma.group_id=mbr_group.id", "LEFT");

    $select_fields = [];
    $user_fields = ["id", "avatar", "nickname", "mobile", "email", "last_login_ip", "last_login_time", "is_locked", "active_score", "total_score", "active_coin", "total_coin"];
    foreach ($user_fields as $idx => $key) {
      array_push($select_fields, "{$prefix}user.{$key}");
    }
    array_push($select_fields, "mbr_group.title AS member_group_title");
    $this->field($select_fields)->group("{$prefix}user.id");

    // 分页参数名是 page ，paginate方法内有获取到该值的方法
    $page = 1;
    if(isset($param["page"])){
      $page = intval($param["page"]);
    }
    $config["page"] = $page;
    $paginate = $this->paginate($page_rows, false, $config);
    return $paginate;
  }

  /**
   * 查询方法
   * @Author zhanghong
   * @Date   2016-12-22
   * @param  [string]     $user_type 用户类型
   * @param  [array]      $param     查询参数
   * @param  [integer]    $page      当前页码
   * @param  integer      $page_rows 每页显示的记录数
   * @return \think\paginator\Collection 
   */
  public function managerSelect($param, $page_rows = 15){
    $prefix = Config::get("database.prefix");
    $this->where("{$prefix}user.user_type", "manager");
    
    $config = [];
    foreach ($param as $key => $value) {
      if(!is_array($value)){
        $value = trim($value);
      }
      switch ($key) {
      case "status":
        if(count($value)){
          $this->where("{$prefix}user.status", intval($value));
        }
        break;
      case "keyword":
        if(empty($value)){
          continue;
        }else{
          $keyword = "%{$value}%";
        }
        $this->where("{$prefix}user.name|{$prefix}user.nickname|{$prefix}user.email|{$prefix}user.mobile", "like", $keyword);
        break;
      }
    }

    $config["query"] = $param;

    $this->join("{$prefix}group_access ga", "ga.uid={$prefix}user.id AND ga.user_type='manager'", "LEFT")
         ->join("{$prefix}auth_group mng_group", "ga.group_id=mng_group.id", "LEFT")
         ->join("{$prefix}group_access ma", "ma.uid={$prefix}user.id AND ma.user_type='member'", "LEFT")
         ->join("{$prefix}auth_group mbr_group", "ma.group_id=mbr_group.id", "LEFT");

    $select_fields = [];
    $user_fields = ["id", "avatar", "nickname", "mobile", "email", "last_login_ip", "last_login_time", "is_locked", "active_score", "total_score", "active_coin", "total_coin"];
    foreach ($user_fields as $idx => $key) {
      array_push($select_fields, "{$prefix}user.{$key}");
    }
    array_push($select_fields, "mng_group.title AS manager_group_title");
    array_push($select_fields, "mbr_group.title AS member_group_title");
    $this->field($select_fields)->group("{$prefix}user.id");

    // 分页参数名是 page ，paginate方法内有获取到该值的方法
    $page = 1;
    if(isset($param["page"])){
      $page = intval($param["page"]);
    }
    $config["page"] = $page;
    $paginate = $this->paginate($page_rows, false, $config);
    // $sql = $this->getLastSql();
    // echo($sql);
    return $paginate;
  }


  /**
   * 验证用户登录密码
   * @Author zhanghong
   * @Date   2017-01-01
   * @param  integer    $id       用户ID
   * @param  string     $password 登录密码
   * @return string               true/false
   */
  public function validPassword($id, $password){
    $count = $this->where("id", $id)->where("status", 1)
                  ->where("password", $this->md5Password($password))
                  ->count();
    if($count){
      return "true";
    }else{
      return "false";
    }
  }

  /**
   * 验证字段值是否唯一
   * @Author zhanghong
   * @Date   2017-01-07
   * @return [type]     [description]
   */
  public function isFieldValueUnique(){
    $is_valid = false;
    $request = Request::instance();
    $id = $request->post("id");
    $field_name = $request->post("field");
    if(empty($field_name)){
      return $is_valid;
    }
    $field_value = $request->post($field_name);
    if(empty($field_value)){
      return $is_valid;
    }
    $this->where("id", "<>", intval($id))->where($field_name, $field_value);
    if(!$this->count()){
      $is_valid = true;
    }
    return $is_valid;
  }

  /**
   * 修改用户类型
   * @Author zhanghong
   * @Date   2017-04-23
   * @param  integer    $id        用户ID
   * @param  string     $user_type 修改后的用户类型
   * @param  integer    $group_id  用户所在组ID
   * @return array
   */
  public function changeUserType($id, $user_type, $group_id = 0){
    $res = ["status" => false, "msg" => "修改用户类型失败"];

    $data = ["id" => $id, "user_type" => $user_type];

    $GroupModel = new AuthGroup();
    if($user_type == "manager"){
      if($group_id < 1){
        $group = $GroupModel->where("group_type", "manager")->where("id", intval($group_id))->find();
        if(empty($group)){
          $res["msg"] = "管理员组不能为空";
          return $res;
        }
      }
      $data["manager_group_id"] = $group_id;
    }else if($user_type == "member"){
      $data["manager_group_id"] = 0;
    }else{
      $res["msg"] = "用户类型不正确";
      return $res;
    }

    
    $this->allowField(true)->update($data);

    // 无论普通用户（member）还是管理员（manager）都有一条user_type='member'的GroupAccess记录
    // 所以，修改用户类型时只需要删除或增加user_type='manager'的GroupAccess
    $AccessModel = new GroupAccess();
    if($user_type == "member"){
      $AccessModel->deleteUserAccess($id, "manager");
    }else{
      $AccessModel->createOrUpdateUserAccess($id, $group_id, "manager");
    }
    $res = ["status" => true, "msg" => "修改用户类型成功"];
    return $res;
  }

  public function exportDemo(){
    // 在ThinkPHP5框架使用PHPExcel实现写Excel文件
    // 在我们项目里PHPExcel是放在项目的extend根目录下
    // 
    // PHPExcel对Excel单元格、表格行、列操作都提供了两种方式
    // 1，参数名是行、列或单元格的名字调用方法， 如getColumnDimension(A)、getStyle('B2')、setCellValue('C1', 'value')
    // 2，按行、列或单元格的column/row下标， 如getColumnDimensionByColumn(1), getStyleByColumnAndRow(1, 1), setCellValueByColumnAndRow(2, 1, 'value')
    // 个人觉得使用行、列或单元格的column/row下标这种方法好一些，特别是当要写的表格超过26列时（第26列的字母名是Z，但27列的字母名是AA）。但当使用column/row下标这种式时需要注意column的起始值是0，而row的起始值是1。
    Loader::import('PHPExcel18.PHPExcel', EXTEND_PATH, '.php');
    Loader::import('PHPExcel18.PHPExcel.Writer.Excel5', EXTEND_PATH);
    Loader::import('PHPExcel18.PHPExcel.IOFactory.php', EXTEND_PATH);
    $date_str = date('Y-m-d', time());
    $objPHPExcel = new \PHPExcel();
    $objPHPExcel->getProperties() 
                 ->setCreator('zhanghong') //设置创建者 
                 ->setLastModifiedBy($date_str) //设置时间 
                 ->setTitle('detail user info') //设置标题 
                 ->setSubject('users contact info') //设置备注 
                 ->setDescription('users contact info') //设置描述 
                 ->setKeywords('userinfo') //设置关键字 | 标记 
                 ->setCategory('users'); //设置类别

    $activeSheet = $objPHPExcel->getActiveSheet();
    $activeSheet->setTitle("注册用户");

    // 合并单元格A1:F1
    // $activeSheet->mergeCells('A1:F1');
    // 与mergeCells('A1:F1')等同，优点是Z列后的名字是‘AA’，自己写程序不好把第27列第一行转成-AA1
    $activeSheet->mergeCellsByColumnAndRow(0, 1, 5, 1);

    // 拆分单元格
    // $activeSheet->unmergeCells('A1:F1');
    // // 与 unmergeCells('A1:F1') 等同
    // $activeSheet->unmergeCellsByColumnAndRow(0, 1, 5, 1);
    
    $activeSheet->getStyle('A2')->getFont()->setName('宋体') //字体
                ->setSize(12) //字体大小
                ->setBold(true); //字体加粗 
    $activeSheet->setCellValue('A2', "字体12号加粗");

    // 设置行高
    $activeSheet->getRowDimension('3')->setRowHeight(12);
    $activeSheet->getRowDimension('4')->setRowHeight(24);

    // 设置列宽
    $activeSheet->getColumnDimension('B')->setWidth(10);
    $activeSheet->getColumnDimension('C')->setWidth(20);

    //长度不够显示的时候 是否自动换行
    $activeSheet->getStyle('B')->getAlignment()->setWrapText(true);
    $activeSheet->setCellValueByColumnAndRow(1, 2, '长度不够显示的时候自动换行');

    //设置打印 页面 方向与大小（此为横向）
    $activeSheet->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    $activeSheet->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

    // 冻结单元格
    //第一种方式
    $activeSheet->freezePane('C5');
    //第二种方式
    $activeSheet->freezePaneByColumnAndRow(2, 5);

    //设置边框
    $activeSheet->getStyle('A1:H8')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
    
    // 设置背景色
    $activeSheet->getStyleByColumnAndRow(4, 3)->getFill()->applyFromArray(array('type' => \PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' =>'C7C7C7')));

    //设置水平居中 
    $activeSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
    $activeSheet->getStyle('B2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //垂直居中
    $activeSheet->getStyle('C1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $activeSheet->getStyle('C2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    //左对齐
    $activeSheet->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
    //右对齐
    $activeSheet->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

    $margin = 1.78/2.54;   //phpexcel 中是按英寸来计算的,所以这里换算了一下
    $margin_right = 1/2.54;   //phpexcel 中是按英寸来计算的,所以这里换算了一下
    $activeSheet->getPageMargins()->setLeft($margin);      //左
    $activeSheet->getPageMargins()->setRight($margin_right);    //右
    //$activeSheet->getPageSetup()->setFitToWidth('1');//自动填充到页面的宽度
    //$activeSheet->getPageSetup()->setFitToHeight('1');//自动填充到页面的高度
    //
    //设置 标题索引    （点击 标题 跳转到对应的工作簿中）
    $activeSheet->getCell('B4')->getHyperlink()->setUrl("sheet://'说明'!A1");
    
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="userinfo_'.date("mdH").'.xls"');
    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');

  }

  /**
   * MD5加密密码
   * @Author zhanghong
   * @Date   2016-12-22
   * @param  string     $password 明文密码
   * @param  string     $authcode 加密key
   * @return string               加密后的密码
   */
  protected function md5Password($password, $authcode=''){
    if(empty($authcode)){
      $authcode = Config::get("authcode");
    }
    $result = "###".md5(md5($authcode.$password));
    return $result;
  }
}